package org.pentaho.di.ui.spoon.dialog;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 * @ClassName: MySQLUtilImpl
 * @Description: TODO
 * @author zengqha
 * @date Nov 22, 2011 5:45:05 PM
 */
public class MySQLUtilImpl implements GerneralSqlUtil {

	public String getTableColumnInfoSql(String tableName) {
		String sql = "desc " + tableName;
		return sql;
	}

	/*
	 * create table user_log ( ID VARCHAR(15) not null primary key, NAME
	 * VARCHAR(60) , age VARCHAR(30) , version VARCHAR(1) , SIP_EXTYPE
	 * numeric(1,0), SIP_DML_TYPE CHAR(1), SIP_EXCUTIME timestamp(14) ); create
	 * table userinfo ( ID VARCHAR(15) not null primary key, NAME VARCHAR(60) ,
	 * age VARCHAR(30) , version VARCHAR(1) );
	 * 
	 * DELIMITER // CREATE trigger I_user AFTER insert on userinfo for each row
	 * Begin insert into user_log (id,name,age,version,SIP_EXTYPE,SIP_DML_TYPE)
	 * values (new.id,new.name,new.age,new.version,'0','I'); end; //; DELIMITER //
	 * CREATE trigger U_user AFTER update on userinfo for each row Begin update
	 * user_log set
	 * id=old.id,name=new.name,age=new.age,version=new.version,SIP_EXTYPE='0',SIP_DML_TYPE='U'
	 * where id=old.id; end; //; DELIMITER // CREATE trigger D_user AFTER delete
	 * on userinfo for each row Begin update user_log set
	 * name=old.name,age=old.age,version=old.version,SIP_EXTYPE='0',SIP_DML_TYPE='D'
	 * where id=old.id; end; //;
	 */
	public String getCreateLogTableSql(String tableName, ArrayList<String> columnNames, ArrayList<String> columnTypes) {
		StringBuffer sql = new StringBuffer(" create table " + LOG_TABLE_HEAD_NAME + tableName + LOG_TABLE_TAIL_NAME + "( ");
		for (int i = 0; i < columnNames.size(); i++) {
			sql.append(columnNames.get(i) + " " + columnTypes.get(i) + ",");
		}
		for (int i = 0; i < INFO_TABLE_FIELDS_MYSQL.length; i++) {
			sql.append(INFO_TABLE_FIELDS_MYSQL[i]);
		}
		return sql.toString();
	}

	public ArrayList<String> getCreateTriggerSql(String tableName, ArrayList<String> columnNames) {
		StringBuffer sql = new StringBuffer();
		String sqlstr = new String();
		ArrayList<String> sqllist = new ArrayList<String>();

		String[] trigType = new String[] { "insert", "update", "delete" };
		String newType = "";
		String optType = "";
		String nameType = "";
		for (String tmp : trigType) {
			if (tmp == "insert") {
				nameType = TRIGGER_HEAD_NAME_MYSQL_INSERT;
				newType = "new.";
				optType = "I";
			}
			if (tmp == "update") {
				nameType = TRIGGER_HEAD_NAME_MYSQL_UPDATE;
				newType = "new.";
				optType = "U";
			}
			if (tmp == "delete") {
				nameType = TRIGGER_HEAD_NAME_MYSQL_DELETE;
				newType = "old.";
				optType = "D";
			}
			sql.append(" CREATE trigger " + nameType + tableName + " AFTER " + tmp + " on " + tableName + " for each row ");
			sql.append(" Begin ");
			sql.append(" declare v_sip_pk_id int; ");
			sql.append(" select ifnull(max(sip_pk_id),0)+1 from " + LOG_TABLE_HEAD_NAME + tableName + LOG_TABLE_TAIL_NAME + " into" + " v_sip_pk_id;");
			sql.append(" insert into " + LOG_TABLE_HEAD_NAME + tableName + LOG_TABLE_TAIL_NAME + "( ");
			for (int i = 0; i < columnNames.size(); i++) {
				sql.append(columnNames.get(i) + ",");
			}
			sql.append("SIP_PK_ID,SIP_EXTYPE,SIP_DML_TYPE,SIP_EXCUTIME) ");
			sql.append(" values(");
			for (int j = 0; j < columnNames.size(); j++) {
				sql.append(newType + columnNames.get(j) + ",");
			}
			sqlstr = sql.append("v_sip_pk_id ,'0','" + optType + "',CURRENT_TIMESTAMP() );end;").toString();
			sqllist.add(sqlstr);
			sql.delete(0, sql.length());
		}

		return sqllist;

	}

	public String getQueryUserTablesSql() {
		String sql = "show tables";
		return sql;
	}

	public ArrayList<ArrayList<String>> getTargetColumnDDL(Connection conn, String tableName) throws Exception {
		ArrayList<ArrayList<String>> result = new ArrayList<ArrayList<String>>();
		ArrayList<String> columnNames = new ArrayList<String>();
		ArrayList<String> columnTypes = new ArrayList<String>();

		Statement query = conn.createStatement();
		ResultSet rs = query.executeQuery(getTableColumnInfoSql(tableName));
		while (rs.next()) {
			columnNames.add(rs.getString(1));
			columnTypes.add(rs.getString(2));
		}
		result.add(columnNames);
		result.add(columnTypes);
		return result;
	}

	@Override
	public String getCreatEexcTableInfoSql() {
		String sql = " create table " + GerneralSqlUtil.lOGTABLE_INFO_TABLE_NAME + "(TABLENAME varchar(255),TABLEDESC varchar(255)) ";
		return sql;
	}

	@Override
	public PrivResult getTriggerPrivs(Connection conn, String username) throws SQLException {
		PrivResult result = new PrivResult();
		result.setDatabaseType("MySQL");
		String selectVersion = "select version()";
		String queryCreateAndSuperPriv = "select t.Create_priv,t.Super_priv from mysql.user t where t.user='" + username + "'";
		String queryTriggerPriv = "select t.trigger_priv from mysql.user t where t.user='" + username + "'";

		ResultSet versionResultSet = conn.createStatement().executeQuery(selectVersion);

		int versionOfInt = 0;
		while (versionResultSet.next()) {
			result.setVersion(versionResultSet.getString(1));
			versionOfInt = Integer.valueOf(result.getVersion().substring(0, 3).replace(".", ""));
		}
		if (versionOfInt < 50) {
			result.setVersion(PrivResult.OLD_VERSION);
		} else {
			ResultSet privilige = conn.createStatement().executeQuery(queryCreateAndSuperPriv);
			privilige.next();
			result.setCreate_priv("Y".equalsIgnoreCase(privilige.getString(1)));
			result.setSuper_priv("Y".equalsIgnoreCase(privilige.getString(2)));

			if (versionOfInt == 50) {
				result.setVersion(PrivResult.TRIGGER_SUPPORT_START_VERSION);
			} else if (versionOfInt > 50) {
				result.setVersion(PrivResult.NEW_VERSION);
				ResultSet triggerPriv = conn.createStatement().executeQuery(queryTriggerPriv);
				privilige.next();
				result.setSuper_priv("Y".equalsIgnoreCase(triggerPriv.getString(1)));
			}
		}
		return result;
	}
}
